package com.qumitech.visualnovel.service.virtualSearch;

import com.alibaba.fastjson.JSONObject;
import com.qumitech.visualnovel.model.dao.console.VisualConnectConfig;
import com.qumitech.visualnovel.model.dao.console.VisualSearchRecord;
import com.qumitech.visualnovel.component.util.DateUtil;
import com.qumitech.visualnovel.component.util.MysqlUtil;
import com.qumitech.visualnovel.component.config.Config;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.scheduling.annotation.AsyncResult;
import org.springframework.stereotype.Component;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.Future;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

@Component
public class VisualSearchTask {
    private static final String EXCEL_DIR = Config.getProperty("excel.user_group.dir");
    private static final Logger log = LoggerFactory.getLogger(VisualSearchTask.class);

    @Autowired
    VisualSearchService visualSearchService;

    @Autowired
    DateUtil dateUtil;

    private void writeHead(SXSSFSheet sheet,JSONObject item) {
        SXSSFRow row0 = sheet.createRow(0);
        int column = 0;
        for(String key: item.keySet()) {
            SXSSFCell cell = row0.createCell(column);
            cell.setCellValue(key);
            column++;
        }
    }

    private void writeRow(SXSSFSheet sheet,int row, JSONObject item) {
        SXSSFRow row0 = sheet.createRow(row);
        int column = 0;
        for(String key: item.keySet()) {
            SXSSFCell cell = row0.createCell(column);
            cell.setCellValue(item.getString(key));
            column++;
        }
    }

    private void writeToExcel(List<JSONObject> records, VisualSearchRecord visualSearchRecord){
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet("可视化查询结果_");
        for (JSONObject record : records) {
            writeHead(sheet,record);
            break;
        }
        int row = 1;
        for (JSONObject record : records) {
            writeRow(sheet, row, record);
            row++;
        }
        String fileName = String.format("%s/可视化查询结果_%d_%s.xlsx", EXCEL_DIR,visualSearchRecord.getId(),dateUtil.getCurrentTime());
        FileOutputStream fo = null; // 输出到文件
        try {
            fo = new FileOutputStream(fileName);
            workbook.write(fo);
            fo.close();
            workbook.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        visualSearchRecord.setDownload(fileName);
        visualSearchRecord.setStatus(1);
    }

    //异步任务此处必须返回Future，可以是Future<String>，也可以是Future<Integer>，自己定义具体的返回内容
    @Async
    public Future<String> executeTask(VisualSearchRecord visualSearchRecord) {
        VisualConnectConfig visualConnectConfig = visualSearchService.getVisualConnectConfigById(visualSearchRecord.getDatabaseId());
        MysqlUtil mysqlCon = new MysqlUtil(visualConnectConfig.getHost(), visualConnectConfig.getUser(), visualConnectConfig.getPassword(), visualConnectConfig.getDb());
        try {
            List<JSONObject> records = mysqlCon.returnMultipleResult(visualSearchRecord.getTaskSql(),null);
            mysqlCon.close();
            //排序
            /*
            records.sort(new Comparator<JSONObject>() {
                private static final String key="weight";
                public int compare(JSONObject a, JSONObject b) {
                    Integer valA=a.getIntValue(key);
                    Integer valB=b.getIntValue(key);
                    return valB.compareTo(valA);
                }
            });
            */
            writeToExcel(records, visualSearchRecord);
            System.out.println("end.......");
            visualSearchService.update(visualSearchRecord);
            return new AsyncResult<>("success");
        } catch (SQLException e) {
            log.error("excute task is error", e);
            visualSearchRecord.setStatus(2);
            visualSearchService.update(visualSearchRecord);
            return new AsyncResult<>(String.format("error: %s", e.toString()));
        }
    }

}
